<?php
namespace app\api\model;
use think\Config;
use think\Db;
class IndexModel extends \app\common\model\CommonModel
{
    /**
     * @param string    $userid
     * @param string    $departmentId
     * @param string    $method
     * @param string    $exact
     * @return array    $res
     */
    public function optionsList($userid, $departmentId, $params)
    {
        try {
            switch ($params['method']) {
                case 'username':        //  用户
                    $sql = "SELECT username,userid,departmentId,email,`state`,department,post FROM enum_user WHERE `state` = 1 ";
                    if(!empty($params['exact'])){
                        $sql .= "AND username LIKE '%".$params['exact']."%'";
                    }
                    $order = "ORDER BY CONVERT(username USING gbk) COLLATE gbk_chinese_ci ASC";
                    $sql .= $order;
                    $res = $this->getRes($sql); 
                    break;
                case 'brand':           //  品牌
                    $sql = "SELECT id,brand,team_video FROM brand WHERE (create_userid = '{$userid}' OR FIND_IN_SET($departmentId, team_video) > 0 OR FIND_IN_SET($departmentId, team_operate) > 0) ";
                    if(!empty($params['exact'])){
                        $sql .= "AND brand LIKE '%".$params['exact']."%'";    
                    }
                    $order = "ORDER BY CONVERT(brand USING gbk) COLLATE gbk_chinese_ci ASC";
                    $sql .= $order;
                    $res = $this->getRes($sql);
                    if($res){
                        foreach ($res as $key => $value) {
                            $res[$key]['products'] = Db::table('product')->where(['brand_id' => $value['id']])->select();
                        }
                    } 
                    break;
                case 'product':         //  产品
                    $sql = "SELECT a.id product_id,a.brand_id,a.product_name,a.advertiser_id,b.brand FROM product a 
                            LEFT JOIN 
                            (SELECT id,brand FROM brand WHERE (create_userid = '{$userid}' OR FIND_IN_SET($departmentId, team_video) > 0 OR FIND_IN_SET($departmentId, team_operate) > 0)) b 
                            ON a.brand_id = b.id WHERE 1=1 ";
                    if(!empty($params['exact'])){
                        $sql .= "AND product_name LIKE '%".$params['exact']."%'";    
                    }
                    $order = "ORDER BY CONVERT(product_name USING gbk) COLLATE gbk_chinese_ci ASC";
                    $sql .= $order;
                    $res = $this->getRes($sql); 
                    break;
                case 'customer':        //  客户
                    
                    // $sql = "SELECT id,customer_id,customer_name FROM enum_customer WHERE 1=1 ";
                    $sql = "SELECT a.id,a.customer_id,a.customer_name FROM enum_customer a INNER JOIN brand b ON a.customer_id = b.customer_id WHERE 
                            (b.create_userid = '{$userid}' OR FIND_IN_SET($departmentId, b.team_video) > 0 OR FIND_IN_SET($departmentId, b.team_operate) > 0) ";
                    if(!empty($params['exact'])){
                        $sql .= "AND a.customer_name LIKE '%".$params['exact']."%' ";    
                    }
                    $order = "ORDER BY CONVERT(a.customer_name USING gbk) COLLATE gbk_chinese_ci ASC";
                    $sql .= $order;
                    $res = $this->getRes($sql); 
                    break;
                case 'all_customer':    //  所有客户
                    $sql = "SELECT id,customer_id,customer_name FROM enum_customer WHERE 1=1 ";
                    if(!empty($params['exact'])){
                        $sql .= "AND customer_name LIKE '%".$params['exact']."%' ";    
                    }
                    $order = "ORDER BY CONVERT(customer_name USING gbk) COLLATE gbk_chinese_ci ASC";
                    $sql .= $order;
                    $res = $this->getRes($sql); 
                    break;
                case 'tasklist':        //  需求清单类型
                    $sql = "SELECT id,`name`,`desc` FROM tasklist_type WHERE 1=1 ";
                    $order = "ORDER BY CONVERT(name USING gbk) COLLATE gbk_chinese_ci ASC";
                    $sql .= $order;
                   $res = $this->getRes($sql);
                    break;
                default:
                    throw new \Exception("参数错误"); 
                    break;
            }
            return $res;    
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }    
    }

    /**
     * @param string    $userid
     * @param string    $departmentId
     * @param string    $type
     */
    public function board($userid,$departmentId,$type)
    {
        try {
            switch ($type) {
                case 'brand':
                    $res = Db::table('brand')->where("create_userid = '{$userid}' OR FIND_IN_SET($departmentId, team_video) > 0 OR FIND_IN_SET($departmentId, team_operate) > 0")->count();
                    break;
                case 'video':
                    // $res = Db::table('video')->where("FIND_IN_SET($departmentId, b.team_video) > 0")->count();
                    $res = Db::table('video')
                                ->alias('a')
                                ->join('brand b','a.brand_id = b.id')
                                ->where("FIND_IN_SET($departmentId, b.team_video) > 0 OR FIND_IN_SET($departmentId, b.team_operate) > 0")
                                ->count();
                    break;
                default:
                    throw new \Exception("type参数错误");
                    break;
            }
            return ['result' => $res];
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage()); 
        }
    }

    /**
     * 视频消耗列表
     * @param int userid
     * @param int departmentId
     * @param array params
     * @return array
     */
    public function getList($userid,$departmentId,$params)
    {
        try {
            $page     = isset($params['page']) && is_numeric($params['page']) ? (int)$params['page'] : 1;
            $pagesize = isset($params['pagesize']) && is_numeric($params['pagesize']) ? (int)$params['pagesize'] : 10;
            $start = ($page-1) * $pagesize;

            $startTime  = !empty($params['startTime']) ? $params['startTime'] : strtotime('today midnight') * 1000; 
            $endTime    = !empty($params['endTime']) ? $params['endTime'] : (strtotime('tomorrow midnight') - 1) * 1000;

            $metrics = explode(',', $params['fields']);
            
            $field = "a.tos_url,a.poster,a.material_id mid,a.direct_userid,a.camera_userid,b.brand,c.username,d.product_name,e.username direct_username,f.username camera_username";
            $sql = "SELECT {$field} FROM video a LEFT JOIN brand b ON a.brand_id = b.id 
                    LEFT JOIN user c ON a.upload_userid = c.userid 
                    LEFT JOIN product d ON a.product_id = d.id 
                    LEFT JOIN enum_user e ON a.direct_userid = e.userid AND a.direct_userid <> '' 
                    LEFT JOIN enum_user f ON a.camera_userid = f.userid AND a.direct_userid <> '' WHERE (FIND_IN_SET($departmentId, b.team_video) > 0 OR FIND_IN_SET($departmentId, b.team_operate) > 0) ";
            // 用户名
            if(!empty($params['username'])){
                $sql .= "AND c.username = '{$params['username']}' ";
            }
            // 编导
            if(!empty($params['direct_userid'])){
                $sql .= "AND a.direct_userid = '{$params['direct_userid']}' ";
            }
            // 摄像
            if(!empty($params['camera_userid'])){
                $sql .= "AND a.camera_userid = '{$params['camera_userid']}' ";
            }
            // 品牌名
            if(!empty($params['brand'])){
                $sql .= "AND b.brand = '{$params['brand']}' ";
            }
            // 产品名
            if(!empty($params['product'])){
                $sql .= "AND d.product_name = '{$params['product']}' ";
            }
            // 视频名
            if(!empty($params['exact'])){
                $sql .= "AND a.name LIKE '%{$params['exact']}%' ";
            }
            $res = $this->getRes($sql);
            if(empty($res)){
                return $this->pageData([],$pagesize,$page,0);
            }
            
            $mids = array_column($res,'material_id');
            
            $postData = [
                'startTime' => $startTime,
                'endTime'   => $endTime,
                'mids'      => $res,
                'metrics'   => $metrics,
                'page'      => $page,
                'page_size' => $pagesize,
                'type'      => 'list',
            ];
            
            $ret = httpRequest("https://fc.souxiu.cn/video_pretest/video_datalist",json_encode($postData));
            
            if(isset($ret['code']) && $ret['code'] == 0 && $ret['msg'] == 'ok' && !empty($ret['data']['materials']) && !empty($ret['data']['pagination'])){
                $data = $ret['data']['materials'];
                $count = (int)$ret['data']['pagination']['total'];
                return $this->pageData($data,$pagesize,$page,$count);
            }elseif ($ret['msg'] !== 'ok') {
                throw new \Exception($ret['msg']); 
            }else {
                return $this->pageData([],$pagesize,$page,0);
            }
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage()); 
        }
    }

    /**
     * 下载时获取消耗数据
     * 
     */
    public function getExportData($userid,$departmentId,$params)
    {
        try {
            $startTime  = !empty($params['startTime']) ? $params['startTime'] : strtotime('today midnight') * 1000; 
            $endTime    = !empty($params['endTime']) ? $params['endTime'] : (strtotime('tomorrow midnight') - 1) * 1000;

            $metrics = explode(',', $params['fields']);
            
            $field = "a.tos_url,a.poster,a.material_id mid,a.direct_userid,a.camera_userid,b.brand,c.username,d.product_name,e.username direct_username,f.username camera_username";
            $sql = "SELECT {$field} FROM video a LEFT JOIN brand b ON a.brand_id = b.id 
                    LEFT JOIN user c ON a.upload_userid = c.userid 
                    LEFT JOIN product d ON a.product_id = d.id 
                    LEFT JOIN enum_user e ON a.direct_userid = e.userid AND a.direct_userid <> '' 
                    LEFT JOIN enum_user f ON a.camera_userid = f.userid AND a.direct_userid <> '' WHERE (FIND_IN_SET($departmentId, b.team_video) > 0 OR FIND_IN_SET($departmentId, b.team_operate) > 0) ";
            // 用户名
            if(!empty($params['username'])){
                $sql .= "AND c.username = '{$params['username']}' ";
            }
            // 编导
            if(!empty($params['direct_userid'])){
                $sql .= "AND a.direct_userid = '{$params['direct_userid']}' ";
            }
            // 摄像
            if(!empty($params['camera_userid'])){
                $sql .= "AND a.camera_userid = '{$params['camera_userid']}' ";
            }
            // 品牌名
            if(!empty($params['brand'])){
                $sql .= "AND b.brand = '{$params['brand']}' ";
            }
            // 产品名
            if(!empty($params['product'])){
                $sql .= "AND d.product_name = '{$params['product']}' ";
            }
            // 视频名
            if(!empty($params['exact'])){
                $sql .= "AND a.name LIKE '%{$params['exact']}%' ";
            }
            
            $res = $this->getRes($sql);
            if(empty($res)){
                return $this->pageData([],$pagesize,$page,0);
            }
            
            $mids = array_column($res,'material_id');
            
            $postData = [
                'startTime' => $startTime,
                'endTime'   => $endTime,
                'mids'      => $res,
                'metrics'   => $metrics,
                'type'      => 'export',
            ];
            
            $ret = httpRequest("https://fc.souxiu.cn/video_pretest/video_datalist",json_encode($postData));
            
            if(isset($ret['code']) && $ret['code'] == 0 && $ret['msg'] == 'ok' && !empty($ret['data']['materials']) && !empty($ret['data']['pagination'])){
                // 处理数据
                return $this->handle_download_data($ret['data']['materials'], $startTime, $endTime);
            }elseif ($ret['msg'] !== 'ok') {
                throw new \Exception($ret['msg']); 
            }else {
                return [];
            }
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage()); 
        }    
    }

    /**
     * 处理下载字段
     * @param   array   $params
     */
    public function handle_download_fields($params)
    {
        try {
            $fields = $params['fields'];
            $state = !empty($params['state']) && in_array($params['state'],['unidesk','juliang']) ? $params['state'] : 'juliang';
            if ($state == 'juliang') {
                $commonfields   = commonFieldJuliang();
                $en_field       = array_merge($commonfields,explode(',',$fields));
                $headerfields   = enToCnFieldJuliang();
                $cn_header = [];
                foreach($en_field as $key => $value){
                    $cn_header[] = $headerfields[$value];
                }
                $coloumheader = array_slice(headerFields(),0,count($en_field));
                $fieldarr = [
                    'en_field'      => $en_field,
                    'cn_header'     => $cn_header,
                    'coloumheader'  => $coloumheader,
                    'en_to_cn'      => array_combine($coloumheader,$en_field),
                ];  
            }elseif ($state == 'unidesk') {
                $commonfields   = commonField();
                $en_field       = array_merge($commonfields,explode(',',$fields));
                $headerfields   = enToCnField();
                $cn_header = [];
                foreach($en_field as $key => $value){
                    $cn_header[] = $headerfields[$value];
                }
                $coloumheader = array_slice(headerFields(),0,count($en_field));
                $fieldarr = [
                    'en_field'      => $en_field,
                    'cn_header'     => $cn_header,
                    'coloumheader'  => $coloumheader,
                    'en_to_cn'      => array_combine($coloumheader,$en_field),
                ];
            }else {
                throw new \Exception('state参数错误');
            }
            return $fieldarr;       
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 获取下载标题
     * @param   array   $params
     */
    public function handle_download_title($params)
    {
        try {
            $startTime = !empty($params['startTime']) ? date('Ymd', substr($params['startTime'], 0, 10)) : date('Ymd');
            $endTime = !empty($params['endTime']) ? date('Ymd', substr($params['endTime'], 0, 10))  : date('Ymd');
            $d = $startTime . '-' . $endTime;
            $state  = !empty($params['state']) && in_array($params['state'],['unidesk','juliang']) ? $params['state'] : 'juliang';
            if ($state == 'unidesk') {
                return 'UD消耗数据-' . $d . ' ' .date('His');    
            }elseif ($state == 'juliang') {
                return '巨量消耗数据-' . $d . ' ' .date('His'); 
            }else {
                return "消耗数据-" . $d . ' ' .date('His');    
            }
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }    
    }

    /**
     * 处理下载数据
     * @param   array   $data
     */
    public function handle_download_data($data, $start, $end)
    {
        $start  = date('Y-m-d', substr($start, 0, 10));
        $end    = date('Y-m-d', substr($end, 0, 10));
        $res = [];
        if (!empty($data)) {
            foreach ($data as $key => $value) {
                $res[$key]                              = array_merge($value['materialBaseInfo'],$value['metrics']);
                $res[$key]['mid']                       = !empty($value['materialBaseInfo']['mid']) ? $value['materialBaseInfo']['mid'] . ' ' : '-';
                $res[$key]['camera_username']           = !empty($value['materialBaseInfo']['camera_username']) ? $value['materialBaseInfo']['camera_username'] : '-';
                $res[$key]['direct_username']           = !empty($value['materialBaseInfo']['direct_username']) ? $value['materialBaseInfo']['direct_username'] : '-';
                $res[$key]['createTime']                = !empty($value['materialBaseInfo']['createTime']) ? date('Y-m-d H:i:s',$value['materialBaseInfo']['createTime']) : '-';
                $res[$key]['tos_url']                   = !empty($value['materialBaseInfo']['tos_url']) ? "https://video.souxiu.cn/play?video=" . str_replace("https://video-souxiu-cn.tos-cn-beijing.volces.com/video/", "", $value['materialBaseInfo']['tos_url']) : '-';
                $res[$key]['date']                      = $start . '~' . $end;
                $res[$key]['valid_play_rate']           = !empty($value['metrics']['valid_play_rate']) && $value['metrics']['valid_play_rate'] != '0.000000' ? $value['metrics']['valid_play_rate'] + 0 . '%' : '-';
                $res[$key]['play_over_rate']            = !empty($value['metrics']['play_over_rate']) && $value['metrics']['play_over_rate'] != '0.000000' ? $value['metrics']['play_over_rate'] + 0 . '%' : '-';
                $res[$key]['play_duration_2s_rate']     = !empty($value['metrics']['play_duration_2s_rate']) && $value['metrics']['play_duration_2s_rate'] != '0.000000' ? $value['metrics']['play_duration_2s_rate'] + 0 . '%' : '-';
                $res[$key]['play_duration_3s_rate']     = !empty($value['metrics']['play_duration_3s_rate']) && $value['metrics']['play_duration_3s_rate'] != '0.000000' ? $value['metrics']['play_duration_3s_rate'] + 0 . '%' : '-';
                $res[$key]['play_duration_5s_rate']     = !empty($value['metrics']['play_duration_5s_rate']) && $value['metrics']['play_duration_5s_rate'] != '0.000000' ? $value['metrics']['play_duration_5s_rate'] + 0 . '%' : '-';
                $res[$key]['play_duration_10s_rate']    = !empty($value['metrics']['play_duration_10s_rate']) && $value['metrics']['play_duration_10s_rate'] != '0.000000' ? $value['metrics']['play_duration_10s_rate'] + 0 . '%' : '-';
                $res[$key]['play_25_feed_break_rate']   = !empty($value['metrics']['play_25_feed_break_rate']) && $value['metrics']['play_25_feed_break_rate'] != '0.000000' ? $value['metrics']['play_25_feed_break_rate'] + 0 . '%' : '-';
                $res[$key]['play_50_feed_break_rate']   = !empty($value['metrics']['play_50_feed_break_rate']) && $value['metrics']['play_50_feed_break_rate'] != '0.000000' ? $value['metrics']['play_50_feed_break_rate'] + 0 . '%' : '-';
                $res[$key]['play_75_feed_break_rate']   = !empty($value['metrics']['play_75_feed_break_rate']) && $value['metrics']['play_75_feed_break_rate'] != '0.000000' ? $value['metrics']['play_75_feed_break_rate'] + 0 . '%' : '-';
                $res[$key]['play_99_feed_break_rate']   = !empty($value['metrics']['play_99_feed_break_rate']) && $value['metrics']['play_99_feed_break_rate'] != '0.000000' ? $value['metrics']['play_99_feed_break_rate'] + 0 . '%' : '-';
                $res[$key]['ctr']                       = !empty($value['metrics']['ctr']) && $value['metrics']['ctr'] != '0.000000' ? $value['metrics']['ctr'] + 0 . '%' : '-';
                $res[$key]['conversion_rate']           = !empty($value['metrics']['conversion_rate']) && $value['metrics']['conversion_rate'] != '0.000000' ? $value['metrics']['conversion_rate'] + 0 . '%' : '-';
                $res[$key]['deep_convert_rate']         = !empty($value['metrics']['deep_convert_rate']) && $value['metrics']['deep_convert_rate'] != '0.000000' ? $value['metrics']['deep_convert_rate'] + 0 . '%' : '-';
                $res[$key]['convert_show_rate']         = !empty($value['metrics']['convert_show_rate']) && $value['metrics']['convert_show_rate'] != '0.000000' ? $value['metrics']['convert_show_rate'] + 0 . '%' : '-';
            }
        }
        return $res;
    }
}